{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Class06"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Execucomp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = '/Users/ml/Google Drive/af/teaching/database/data/'\n",
    "execucomp_raw = pd.read_csv(file_path+'execucomp_1992_2017.txt',sep='\\t',engine='python')\n",
    "execucomp_raw.columns = execucomp_raw.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>exec_fullname</th>\n",
       "      <th>cfoann</th>\n",
       "      <th>execdir</th>\n",
       "      <th>co_per_rol</th>\n",
       "      <th>coname</th>\n",
       "      <th>ceoann</th>\n",
       "      <th>salary</th>\n",
       "      <th>total_sec</th>\n",
       "      <th>shrown_excl_opts_pct</th>\n",
       "      <th>age</th>\n",
       "      <th>...</th>\n",
       "      <th>becameceo</th>\n",
       "      <th>joined_co</th>\n",
       "      <th>leftofc</th>\n",
       "      <th>leftco</th>\n",
       "      <th>pceo</th>\n",
       "      <th>pcfo</th>\n",
       "      <th>gender</th>\n",
       "      <th>page</th>\n",
       "      <th>cusip</th>\n",
       "      <th>sic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ira A. Eichner</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>5622</td>\n",
       "      <td>AAR CORP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>460.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>19550101.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19961001.0</td>\n",
       "      <td>19990531.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>87.0</td>\n",
       "      <td>00036110</td>\n",
       "      <td>5080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>5623</td>\n",
       "      <td>AAR CORP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>322.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>19961009.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20180531.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CEO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>65.0</td>\n",
       "      <td>00036110</td>\n",
       "      <td>5080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Philip C. Slapke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5624</td>\n",
       "      <td>AAR CORP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>205.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00036110</td>\n",
       "      <td>5080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Howard A. Pulsifer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5625</td>\n",
       "      <td>AAR CORP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>159.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>75.0</td>\n",
       "      <td>00036110</td>\n",
       "      <td>5080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Timothy J. Romenesko</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5626</td>\n",
       "      <td>AAR CORP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>109.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CFO</td>\n",
       "      <td>MALE</td>\n",
       "      <td>61.0</td>\n",
       "      <td>00036110</td>\n",
       "      <td>5080</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          exec_fullname cfoann  execdir  co_per_rol    coname ceoann  salary  \\\n",
       "0        Ira A. Eichner    NaN        1        5622  AAR CORP    NaN   460.4   \n",
       "1       David P. Storch    NaN        1        5623  AAR CORP    NaN   322.3   \n",
       "2      Philip C. Slapke    NaN        0        5624  AAR CORP    NaN   205.9   \n",
       "3    Howard A. Pulsifer    NaN        0        5625  AAR CORP    NaN   159.1   \n",
       "4  Timothy J. Romenesko    NaN        0        5626  AAR CORP    NaN   109.0   \n",
       "\n",
       "   total_sec  shrown_excl_opts_pct  age  ...    becameceo  joined_co  \\\n",
       "0        NaN                   2.7  NaN  ...   19550101.0        NaN   \n",
       "1        NaN                   NaN  NaN  ...   19961009.0        NaN   \n",
       "2        NaN                   NaN  NaN  ...          NaN        NaN   \n",
       "3        NaN                   NaN  NaN  ...          NaN        NaN   \n",
       "4        NaN                   NaN  NaN  ...          NaN        NaN   \n",
       "\n",
       "      leftofc      leftco  pceo  pcfo  gender  page     cusip   sic  \n",
       "0  19961001.0  19990531.0   NaN   NaN    MALE  87.0  00036110  5080  \n",
       "1  20180531.0         NaN   CEO   NaN    MALE  65.0  00036110  5080  \n",
       "2         NaN         NaN   NaN   NaN    MALE   NaN  00036110  5080  \n",
       "3         NaN         NaN   NaN   NaN    MALE  75.0  00036110  5080  \n",
       "4         NaN         NaN   NaN   CFO    MALE  61.0  00036110  5080  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execucomp_raw.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "execucomp = execucomp_raw.copy()\n",
    "execucomp = execucomp.sort_values(['gvkey','year','execid']).reset_index(drop=True)\n",
    "execucomp = execucomp.drop_duplicates(['gvkey','year','execid'])\n",
    "execucomp = execucomp.sort_values(['gvkey','year']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Historical CEO\n",
    "**ceoann** is the indicator to track historical CEO. **pceo** is the indicator to identify current CEO."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "ceo = execucomp[execucomp['ceoann']=='CEO'].copy()\n",
    "ceo = ceo.sort_values(['gvkey','year']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Historical CFO\n",
    "**cfoann** is the indicator to track historical CFO. **pcfo** is the indicator to identify current CFO."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "cfo = execucomp[execucomp['cfoann']=='CFO'].copy()\n",
    "cfo = cfo.sort_values(['gvkey','year']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Male vs female executives"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "</style>  \n",
       "<table id=\"T_c435de58_a159_11e8_b482_f45c898d729b\" > \n",
       "<thead>    <tr> \n",
       "        <th class=\"index_name level0\" >measure</th> \n",
       "        <th class=\"col_heading level0 col0\" >percent</th> \n",
       "    </tr>    <tr> \n",
       "        <th class=\"index_name level0\" >gender</th> \n",
       "        <th class=\"blank\" ></th> \n",
       "    </tr></thead> \n",
       "<tbody>    <tr> \n",
       "        <th id=\"T_c435de58_a159_11e8_b482_f45c898d729blevel0_row0\" class=\"row_heading level0 row0\" >FEMALE</th> \n",
       "        <td id=\"T_c435de58_a159_11e8_b482_f45c898d729brow0_col0\" class=\"data row0 col0\" >6.02%</td> \n",
       "    </tr>    <tr> \n",
       "        <th id=\"T_c435de58_a159_11e8_b482_f45c898d729blevel0_row1\" class=\"row_heading level0 row1\" >MALE</th> \n",
       "        <td id=\"T_c435de58_a159_11e8_b482_f45c898d729brow1_col0\" class=\"data row1 col0\" >93.98%</td> \n",
       "    </tr></tbody> \n",
       "</table> "
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x11e8ef320>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execucomp['measure'] = 'percent'\n",
    "pd.crosstab(execucomp['measure'],execucomp['gender']).apply(lambda x: x/x.sum(),axis=1).T \\\n",
    "    .style.format('{:.2%}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Male vs female CEO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "</style>  \n",
       "<table id=\"T_c43e92da_a159_11e8_a77a_f45c898d729b\" > \n",
       "<thead>    <tr> \n",
       "        <th class=\"index_name level0\" >measure</th> \n",
       "        <th class=\"col_heading level0 col0\" >percent</th> \n",
       "    </tr>    <tr> \n",
       "        <th class=\"index_name level0\" >gender</th> \n",
       "        <th class=\"blank\" ></th> \n",
       "    </tr></thead> \n",
       "<tbody>    <tr> \n",
       "        <th id=\"T_c43e92da_a159_11e8_a77a_f45c898d729blevel0_row0\" class=\"row_heading level0 row0\" >FEMALE</th> \n",
       "        <td id=\"T_c43e92da_a159_11e8_a77a_f45c898d729brow0_col0\" class=\"data row0 col0\" >2.22%</td> \n",
       "    </tr>    <tr> \n",
       "        <th id=\"T_c43e92da_a159_11e8_a77a_f45c898d729blevel0_row1\" class=\"row_heading level0 row1\" >MALE</th> \n",
       "        <td id=\"T_c43e92da_a159_11e8_a77a_f45c898d729brow1_col0\" class=\"data row1 col0\" >97.78%</td> \n",
       "    </tr></tbody> \n",
       "</table> "
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x11e107940>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ceo['measure'] = 'percent'\n",
    "pd.crosstab(ceo['measure'],ceo['gender']).apply(lambda x: x/x.sum(),axis=1).T.style.format('{:.2%}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CEO tenure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gvkey</th>\n",
       "      <th>year</th>\n",
       "      <th>exec_fullname</th>\n",
       "      <th>tenure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1004</td>\n",
       "      <td>1994</td>\n",
       "      <td>Ira A. Eichner</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1004</td>\n",
       "      <td>1995</td>\n",
       "      <td>Ira A. Eichner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1004</td>\n",
       "      <td>1996</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>1997</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>1998</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1004</td>\n",
       "      <td>1999</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1004</td>\n",
       "      <td>2000</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1004</td>\n",
       "      <td>2001</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1004</td>\n",
       "      <td>2002</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1004</td>\n",
       "      <td>2003</td>\n",
       "      <td>David P. Storch</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gvkey  year    exec_fullname  tenure\n",
       "0   1004  1994   Ira A. Eichner       1\n",
       "1   1004  1995   Ira A. Eichner       2\n",
       "2   1004  1996  David P. Storch       1\n",
       "3   1004  1997  David P. Storch       2\n",
       "4   1004  1998  David P. Storch       3\n",
       "5   1004  1999  David P. Storch       4\n",
       "6   1004  2000  David P. Storch       5\n",
       "7   1004  2001  David P. Storch       6\n",
       "8   1004  2002  David P. Storch       7\n",
       "9   1004  2003  David P. Storch       8"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ceo['tenure'] = ceo.groupby(['gvkey','execid'])['year'].cumcount() + 1\n",
    "ceo[['gvkey','year','exec_fullname','tenure']].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of executive by firm-year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_exec = execucomp.groupby(['gvkey','year'])['execid'].count().to_frame('n_exec')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Percent of female executives by firm-year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "pct_female = execucomp.groupby(['gvkey','year','gender'])['execid'].count().unstack().reset_index()\n",
    "pct_female['FEMALE'] = np.where(pct_female['FEMALE'].isnull(),0,pct_female['FEMALE'])\n",
    "pct_female['pct_female'] = pct_female['FEMALE'] / (pct_female['FEMALE']+pct_female['MALE'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CEO ownership and top5 executives ownership"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "execucomp['shrown_excl_opts_pct'] = execucomp['shrown_excl_opts_pct'] / 100\n",
    "ceoown = execucomp[execucomp['ceoann']=='CEO'].groupby(['gvkey','year'])['shrown_excl_opts_pct'] \\\n",
    "    .sum(min_count=1).to_frame('ceoown').reset_index()\n",
    "top5own = execucomp[execucomp['execrankann']<=5].groupby(['gvkey','year'])['shrown_excl_opts_pct'] \\\n",
    "    .sum(min_count=1).to_frame('top5own').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "execucomp_1 = pct_female.merge(ceoown,how='left',on=['gvkey','year']).reset_index()\n",
    "execucomp_1 = execucomp_1.sort_values(['gvkey','year']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ROA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "roa_raw = pd.read_csv(file_path+'roa.txt',sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "roa = roa_raw.copy()\n",
    "roa['calyr'] = (roa['datadate']/10000).astype(int)\n",
    "roa = roa.sort_values(['gvkey','datadate']).reset_index(drop=True)\n",
    "roa = roa.drop_duplicates(['gvkey','calyr'],keep='last')\n",
    "roa = roa.sort_values(['gvkey','calyr']).reset_index(drop=True)\n",
    "roa['at_l1'] = roa.groupby('gvkey')['at'].shift(1)\n",
    "roa['seq_l1'] = roa.groupby('gvkey')['seq'].shift(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ROA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "roa['roa'] = roa['ib'] / roa['at_l1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ROE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "roa['roe'] = roa['ib'] / roa['seq_l1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Leverage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "roa['lev'] = roa['lt'] / roa['at']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in ['roa','roe','lev']:\n",
    "    roa[i] = np.where((roa[i]==np.inf)|(roa[i]==-np.inf),np.nan,roa[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import statsmodels.formula.api as sm\n",
    "from linearmodels import PanelOLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "execucomp_2 = execucomp_1.merge(roa[['gvkey','fyear','roa','roe','lev']],how='inner', \\\n",
    "    left_on=['gvkey','year'],right_on=['gvkey','fyear'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### OLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"simpletable\">\n",
       "<caption>OLS Regression Results</caption>\n",
       "<tr>\n",
       "  <th>Dep. Variable:</th>           <td>roa</td>       <th>  R-squared:         </th> <td>   0.001</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Model:</th>                   <td>OLS</td>       <th>  Adj. R-squared:    </th> <td>   0.001</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Method:</th>             <td>Least Squares</td>  <th>  F-statistic:       </th> <td>   16.90</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Date:</th>             <td>Thu, 16 Aug 2018</td> <th>  Prob (F-statistic):</th> <td>4.61e-08</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time:</th>                 <td>14:39:16</td>     <th>  Log-Likelihood:    </th> <td>  1017.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>No. Observations:</th>      <td> 30159</td>      <th>  AIC:               </th> <td>  -2029.</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Df Residuals:</th>          <td> 30156</td>      <th>  BIC:               </th> <td>  -2004.</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Df Model:</th>              <td>     2</td>      <th>                     </th>     <td> </td>   \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Covariance Type:</th>      <td>nonrobust</td>    <th>                     </th>     <td> </td>   \n",
       "</tr>\n",
       "</table>\n",
       "<table class=\"simpletable\">\n",
       "<tr>\n",
       "       <td></td>         <th>coef</th>     <th>std err</th>      <th>t</th>      <th>P>|t|</th>  <th>[0.025</th>    <th>0.975]</th>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Intercept</th>  <td>    0.0349</td> <td>    0.002</td> <td>   20.815</td> <td> 0.000</td> <td>    0.032</td> <td>    0.038</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>pct_female</th> <td>    0.0336</td> <td>    0.012</td> <td>    2.866</td> <td> 0.004</td> <td>    0.011</td> <td>    0.057</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>ceoown</th>     <td>    0.0971</td> <td>    0.019</td> <td>    5.140</td> <td> 0.000</td> <td>    0.060</td> <td>    0.134</td>\n",
       "</tr>\n",
       "</table>\n",
       "<table class=\"simpletable\">\n",
       "<tr>\n",
       "  <th>Omnibus:</th>       <td>72310.238</td> <th>  Durbin-Watson:     </th>    <td>   1.412</td>   \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Prob(Omnibus):</th>  <td> 0.000</td>   <th>  Jarque-Bera (JB):  </th> <td>1772281663.706</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Skew:</th>           <td>-24.389</td>  <th>  Prob(JB):          </th>    <td>    0.00</td>   \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Kurtosis:</th>      <td>1189.580</td>  <th>  Cond. No.          </th>    <td>    14.1</td>   \n",
       "</tr>\n",
       "</table><br/><br/>Warnings:<br/>[1] Standard Errors assume that the covariance matrix of the errors is correctly specified."
      ],
      "text/plain": [
       "<class 'statsmodels.iolib.summary.Summary'>\n",
       "\"\"\"\n",
       "                            OLS Regression Results                            \n",
       "==============================================================================\n",
       "Dep. Variable:                    roa   R-squared:                       0.001\n",
       "Model:                            OLS   Adj. R-squared:                  0.001\n",
       "Method:                 Least Squares   F-statistic:                     16.90\n",
       "Date:                Thu, 16 Aug 2018   Prob (F-statistic):           4.61e-08\n",
       "Time:                        14:39:16   Log-Likelihood:                 1017.3\n",
       "No. Observations:               30159   AIC:                            -2029.\n",
       "Df Residuals:                   30156   BIC:                            -2004.\n",
       "Df Model:                           2                                         \n",
       "Covariance Type:            nonrobust                                         \n",
       "==============================================================================\n",
       "                 coef    std err          t      P>|t|      [0.025      0.975]\n",
       "------------------------------------------------------------------------------\n",
       "Intercept      0.0349      0.002     20.815      0.000       0.032       0.038\n",
       "pct_female     0.0336      0.012      2.866      0.004       0.011       0.057\n",
       "ceoown         0.0971      0.019      5.140      0.000       0.060       0.134\n",
       "==============================================================================\n",
       "Omnibus:                    72310.238   Durbin-Watson:                   1.412\n",
       "Prob(Omnibus):                  0.000   Jarque-Bera (JB):       1772281663.706\n",
       "Skew:                         -24.389   Prob(JB):                         0.00\n",
       "Kurtosis:                    1189.580   Cond. No.                         14.1\n",
       "==============================================================================\n",
       "\n",
       "Warnings:\n",
       "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n",
       "\"\"\""
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sm.ols('roa~pct_female+ceoown',execucomp_2).fit().summary()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Panel regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"simpletable\">\n",
       "<caption>PanelOLS Estimation Summary</caption>\n",
       "<tr>\n",
       "  <th>Dep. Variable:</th>           <td>roa</td>       <th>  R-squared:         </th>     <td>0.0002</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Estimator:</th>            <td>PanelOLS</td>     <th>  R-squared (Between):</th>    <td>-0.0014</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>No. Observations:</th>       <td>30159</td>      <th>  R-squared (Within):</th>     <td>0.0002</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Date:</th>             <td>Thu, Aug 16 2018</td> <th>  R-squared (Overall):</th>    <td>0.0005</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time:</th>                 <td>14:39:17</td>     <th>  Log-likelihood     </th>     <td>7272.7</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Cov. Estimator:</th>      <td>Unadjusted</td>    <th>                     </th>        <td></td>     \n",
       "</tr>\n",
       "<tr>\n",
       "  <th></th>                          <td></td>         <th>  F-statistic:       </th>     <td>2.6833</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Entities:</th>               <td>3685</td>       <th>  P-value            </th>     <td>0.0684</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Avg Obs:</th>               <td>8.1843</td>      <th>  Distribution:      </th>   <td>F(2,26879)</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Min Obs:</th>               <td>0.0000</td>      <th>                     </th>        <td></td>     \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Max Obs:</th>               <td>26.000</td>      <th>  F-statistic (robust):</th>   <td>2.6833</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th></th>                          <td></td>         <th>  P-value            </th>     <td>0.0684</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time periods:</th>            <td>26</td>        <th>  Distribution:      </th>   <td>F(2,26879)</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Avg Obs:</th>               <td>1160.0</td>      <th>                     </th>        <td></td>     \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Min Obs:</th>               <td>109.00</td>      <th>                     </th>        <td></td>     \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Max Obs:</th>               <td>1917.0</td>      <th>                     </th>        <td></td>     \n",
       "</tr>\n",
       "<tr>\n",
       "  <th></th>                          <td></td>         <th>                     </th>        <td></td>     \n",
       "</tr>\n",
       "</table>\n",
       "<table class=\"simpletable\">\n",
       "<caption>Parameter Estimates</caption>\n",
       "<tr>\n",
       "       <td></td>      <th>Parameter</th> <th>Std. Err.</th> <th>T-stat</th>  <th>P-value</th> <th>Lower CI</th> <th>Upper CI</th>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Intercept</th>   <td>0.0390</td>    <td>0.0019</td>   <td>20.633</td>  <td>0.0000</td>   <td>0.0353</td>   <td>0.0427</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>pct_female</th>  <td>-0.0116</td>   <td>0.0169</td>   <td>-0.6855</td> <td>0.4930</td>   <td>-0.0446</td>  <td>0.0215</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>ceoown</th>      <td>0.0635</td>    <td>0.0291</td>   <td>2.1793</td>  <td>0.0293</td>   <td>0.0064</td>   <td>0.1206</td> \n",
       "</tr>\n",
       "</table><br/><br/>F-test for Poolability: 4.2169<br/>P-value: 0.0000<br/>Distribution: F(3277,26879)<br/><br/>Included effects: Entity"
      ],
      "text/plain": [
       "<class 'linearmodels.compat.statsmodels.Summary'>\n",
       "\"\"\"\n",
       "                          PanelOLS Estimation Summary                           \n",
       "================================================================================\n",
       "Dep. Variable:                    roa   R-squared:                        0.0002\n",
       "Estimator:                   PanelOLS   R-squared (Between):             -0.0014\n",
       "No. Observations:               30159   R-squared (Within):               0.0002\n",
       "Date:                Thu, Aug 16 2018   R-squared (Overall):              0.0005\n",
       "Time:                        14:39:17   Log-likelihood                    7272.7\n",
       "Cov. Estimator:            Unadjusted                                           \n",
       "                                        F-statistic:                      2.6833\n",
       "Entities:                        3685   P-value                           0.0684\n",
       "Avg Obs:                       8.1843   Distribution:                 F(2,26879)\n",
       "Min Obs:                       0.0000                                           \n",
       "Max Obs:                       26.000   F-statistic (robust):             2.6833\n",
       "                                        P-value                           0.0684\n",
       "Time periods:                      26   Distribution:                 F(2,26879)\n",
       "Avg Obs:                       1160.0                                           \n",
       "Min Obs:                       109.00                                           \n",
       "Max Obs:                       1917.0                                           \n",
       "                                                                                \n",
       "                             Parameter Estimates                              \n",
       "==============================================================================\n",
       "            Parameter  Std. Err.     T-stat    P-value    Lower CI    Upper CI\n",
       "------------------------------------------------------------------------------\n",
       "Intercept      0.0390     0.0019     20.633     0.0000      0.0353      0.0427\n",
       "pct_female    -0.0116     0.0169    -0.6855     0.4930     -0.0446      0.0215\n",
       "ceoown         0.0635     0.0291     2.1793     0.0293      0.0064      0.1206\n",
       "==============================================================================\n",
       "\n",
       "F-test for Poolability: 4.2169\n",
       "P-value: 0.0000\n",
       "Distribution: F(3277,26879)\n",
       "\n",
       "Included effects: Entity\n",
       "\"\"\""
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execucomp_3 = execucomp_2.copy()\n",
    "execucomp_3 = execucomp_3.set_index(['gvkey','fyear'])\n",
    "PanelOLS.from_formula('roa~1+pct_female+ceoown+EntityEffects', \\\n",
    "    execucomp_3.dropna(subset=['roa','pct_female','ceoown'])).fit().summary"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
